package com.factory.iamp.utils.helper;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.factory.iamp.pojo.Table;
import com.factory.iamp.utils.exception.APIException;
import com.spire.xls.*;
import com.spire.xls.collections.PicturesCollection;
import com.spire.xls.core.converter.spreadsheet.shapes.PictureCollection;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelHelper {
    private static Matcher getExcelVariables(String content) {
        Pattern r = Pattern.compile("#\\{.*?}");
        return r.matcher(content);
    }

    private static String makeExcelVariable(String content) {
        return content.replace("#{", "").replace("}", "");
    }

    private static List<String> makeIndexList(File savedFile, String fileName) throws Throwable {
        InputStream inputStream = new FileInputStream(savedFile);
        Workbook workbook = null;
        if(fileName.endsWith(".xls")) workbook = new HSSFWorkbook(inputStream);
        if(fileName.endsWith(".xlsx")) workbook = new XSSFWorkbook(inputStream);
        assert workbook != null;
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        List<String> ceilList = new ArrayList<>();
        String ceilContent;
        for(int i = 0; i <= lastRowNum; i++) {
            if(sheet.getRow(i) == null) continue;
            int lastCellNum = sheet.getRow(i).getLastCellNum();
            for(int j = 0; j <= lastCellNum; j++) {
                if(sheet.getRow(i).getCell(j) == null) continue;
                ceilContent = sheet.getRow(i).getCell(j).getStringCellValue().trim();
                Matcher Variables = getExcelVariables(ceilContent);
                while(Variables.find()) ceilList.add(makeExcelVariable(Variables.group()));
            }
        }
        if(ceilList.size() != ceilList.stream().distinct().count()) {
            if(!savedFile.delete()) throw new APIException(ResponseCode.SERVER_FAILED, "文件删除失败");
            throw new APIException(ResponseCode.VALIDATE_FAILED, "文件中有重名字段");
        }
        return ceilList;
    }

    public static String makeFormat(File savedFile, String fileName) throws Throwable {
        return makeFormat(savedFile, fileName, new ArrayList<>());
    }

    public static String makeFormat(File savedFile, String fileName, List<String> mustIndex) throws Throwable {
        List<String> ceilList = makeIndexList(savedFile, fileName);
        if(!ceilList.containsAll(mustIndex))
            throw new APIException(ResponseCode.VALIDATE_FAILED, "文件中缺少必须字段： " + mustIndex.toString());
        return JSON.toJSONString(ceilList);
    }

    public static void fillFileCeil(
            Worksheet sheet,
            int first_row, int last_row,
            int first_col, int last_col,
            JSONArray data
    ) {
        String ceilValue, fillBlock, fillContent;
        JSONObject problem_data = new JSONObject();
        for(int i = first_row;i <= last_row;i++) {
            for(int j = first_col;j <= last_col;j++) {
                ceilValue = sheet.get(i, j).getValue();
                Matcher m = getExcelVariables(ceilValue);
                boolean tag = false, pic_tag = false, problem_tag = false;
                while(m.find()) {
                    fillBlock = makeExcelVariable(m.group());
                    fillContent = "";
                    pic_tag = fillBlock.equals("Scheck") || fillBlock.equals("Ocheck") || fillBlock.equals("Zcheck");
                    problem_tag = fillBlock.equals("Problem");
                    for(Object x: data) {
                        if(((JSONObject)x).containsKey(fillBlock))
                            if(!((JSONObject) x).getString(fillBlock).startsWith("{"))
                                fillContent = ((JSONObject) x).getString(fillBlock);
                        if(problem_tag && ((JSONObject)x).containsKey("problem"))
                            if(((JSONObject) x).getJSONObject("problem") != null)
                                problem_data = ((JSONObject) x).getJSONObject("problem");
                    }
                    ceilValue = ceilValue.replace(m.group(), fillContent);
                    tag = true;
                }
                if(tag) {
                    if(pic_tag) {
                        if(!ceilValue.equals("")) sheet.getPictures().add(
                                i, j, FileUploadHelper.getBasePath() + ceilValue
                        );
//                        BigDecimal k = BigDecimal.valueOf(0.79).divide(
//                                BigDecimal.valueOf(0.58), 10, BigDecimal.ROUND_DOWN
//                        );
//                        ins_pic.setHeight(
//                                (k.multiply(BigDecimal.valueOf(sheet.get(i, j).getRowHeight())).intValue())
//                        );
//                        k = BigDecimal.valueOf(0.78).divide(
//                                BigDecimal.valueOf(0.09), 10, BigDecimal.ROUND_DOWN
//                        );
//                        ins_pic.setWidth(
//                                2 * (k.multiply(BigDecimal.valueOf(sheet.get(i, j).getColumnWidth()))).intValue()
//                        );
                        ceilValue = "";
                    } else if(problem_tag) {
                        TableProblemHelper tableProblemHelper = new TableProblemHelper();
                        ceilValue = tableProblemHelper.fromJSON(problem_data);
                    }
                    while(ceilValue.endsWith("\n")) ceilValue = ceilValue.substring(0, ceilValue.length() - 1);
                    sheet.get(i, j).setValue(ceilValue);
                }
            }
        }
    }

    public static String makeTableFile(
            Table table, List<JSONObject> content_value, List<String> fileList, String savedPath
    ) {
        String file_name = table.getName();
        FileUploadHelper.mkPath(savedPath);
        com.spire.xls.Workbook wb_target = new com.spire.xls.Workbook();
        wb_target.getWorksheets().clear();
        com.spire.xls.Workbook wb_base = new com.spire.xls.Workbook();
        for(String filepath: fileList) {
            wb_base.loadFromFile(filepath);
            wb_target.getWorksheets().addCopy(wb_base.getWorksheets().get(0), WorksheetCopyType.CopyAll);
        }
        Worksheet result_sheet = wb_target.getWorksheets().get(0);
        Worksheet base_sheet;

        JSONObject HeaderTail;

        HeaderTail = JSONObject.parseObject(table.getHeader_value());
        if(HeaderTail.containsKey("exportObj")) HeaderTail = HeaderTail.getJSONObject("exportObj");
        fillFileCeil(
                result_sheet,
                result_sheet.getFirstRow(), result_sheet.getLastRow(),
                result_sheet.getFirstColumn(), result_sheet.getLastColumn(),
                 new JSONArray(Collections.singletonList(HeaderTail))
        );
        int result_b_col = result_sheet.getFirstColumn();
        int result_b_row = result_sheet.getLastRow() + 1;
        int new_last_row, new_last_col;
        int base_b_col, base_e_col, base_b_row, base_e_row;
        CellRange base_range, target_range;
        int body_index = 0, body_id;
        while(wb_target.getWorksheets().size() > 1) {
            base_sheet = wb_target.getWorksheets().get(1);
            base_b_col = base_sheet.getFirstColumn();
            base_b_row = base_sheet.getFirstRow();
            base_e_col = base_sheet.getLastColumn();
            base_e_row = base_sheet.getLastRow();
            new_last_row = result_b_row + base_e_row - base_b_row;
            new_last_col = result_b_col + base_e_col - base_b_col;
            base_range = base_sheet.getCellRange(base_b_row, base_b_col, base_e_row, base_e_col);
            target_range = result_sheet.getCellRange(result_b_row, result_b_col, new_last_row, new_last_col);
            base_sheet.copy(base_range, target_range,true);
            if(body_index == table.getBody_list().size()) {
                HeaderTail = JSONObject.parseObject(table.getTail_value());
                if(HeaderTail.containsKey("exportObj")) HeaderTail = HeaderTail.getJSONObject("exportObj");
                fillFileCeil(
                        result_sheet,
                        result_b_row, new_last_row,
                        result_b_col, new_last_col,
                        new JSONArray(Collections.singletonList(HeaderTail))
                );
            } else {
                body_id = table.getBody_list().get(body_index);
                JSONObject tmp = content_value.get(body_index).getJSONObject("content"), tmp1;
                tmp.put("num", body_index + 1);
                tmp1 = tmp.clone();
                tmp1.remove("content");
                HeaderTail = JSONObject.parseObject(table.getBody_value()).getJSONObject(String.valueOf(body_id));
                if(HeaderTail.containsKey("exportObj")) HeaderTail = HeaderTail.getJSONObject("exportObj");
                fillFileCeil(
                        result_sheet,
                        result_b_row, new_last_row,
                        result_b_col, new_last_col,
                        new JSONArray(Arrays.asList(HeaderTail, tmp1, tmp))
                );
            }
            for(int i = result_sheet.getLastRow(); i >= 1; i--) {
                if (result_sheet.getRows()[i - 1].isBlank() && (!result_sheet.getRows()[i - 1].isGroupedByRow()) &&
                   (!result_sheet.getRows()[i - 1].isGroupedByColumn()) && (!result_sheet.get(i, 1).hasMerged())
                ) result_sheet.deleteRow(i);
                else break;
            }
            result_b_row = result_sheet.getLastRow() + 1;
            base_sheet.remove();
            body_index += 1;
        }
        int fc, lc, fr, lr;
        BigDecimal tmp1, tmp2, k;

        fc = result_sheet.getFirstColumn();
        lc = result_sheet.getLastColumn();
        fr = result_sheet.getFirstRow();
        lr = result_sheet.getLastRow();

        k = BigDecimal.valueOf(0.79).divide(BigDecimal.valueOf(0.83), 10, BigDecimal.ROUND_DOWN);
//        k = BigDecimal.valueOf(0.81).divide(BigDecimal.valueOf(0.71), 10, BigDecimal.ROUND_DOWN);
        for(int i = fc;i <= lc;i += 1) result_sheet.setColumnWidth(i, k.multiply(
                BigDecimal.valueOf(result_sheet.getColumnWidth(i))
        ).doubleValue());

        k = BigDecimal.valueOf(32.5).divide(BigDecimal.valueOf(lr - fr + 1), 10, BigDecimal.ROUND_DOWN);
        for(int i = fr;i <= lr;i += 1) result_sheet.setRowHeight(i, k.multiply(
                BigDecimal.valueOf(result_sheet.getRowHeight(i))
        ).doubleValue());

        Map<Integer, Double> fin_row_h = new HashMap<>();
        for(int i = fr;i <= lr; i++) fin_row_h.put(i, result_sheet.getRowHeight(i));
        double[] diff_col_width = new double[lc + 2];

        diff_col_width[fc] = 0;

        for(int j = fc;j <= lc;j++) diff_col_width[j + 1] = diff_col_width[j] + result_sheet.getColumnWidth(j);

        double single_row_h;
        String values;

        double font_s2h = 1.4, font_s2w = 0.75;
        CellRange cell, cell1;

        for(int i = fr;i <= lr; i++) {
            for(int j = fc;j <= lc;j++) {
                cell1 = result_sheet.getCellRange(i, j);
                if(cell1.getValue().length() == 0) continue;
                values = cell1.getValue();
                if(cell1.getMergeArea() == null) cell = cell1;
                else cell = cell1.getMergeArea();
                double cell_width = diff_col_width[cell.getEndCell().getColumn() + 1] - diff_col_width[cell.getColumn()];
                double font_size = cell.getCellStyle().getExcelFont().getSize();
                cell_width = Math.floor(cell_width * 2.25 / 0.365);
                single_row_h = 0;
                for(String line: values.split("\n"))
                    single_row_h += Math.ceil((line.length() + 1) * font_size * font_s2w / cell_width);
                single_row_h *= font_size * font_s2h / (cell.getEndCell().getRow() + 1 - cell.getRow());
                for(int kk = cell.getRow(); kk <= cell.getEndCell().getRow(); kk++)
                    if(fin_row_h.get(kk) < single_row_h) fin_row_h.put(kk, single_row_h);
            }
        }

        for(int i = fr;i <= lr; i++) result_sheet.setRowHeight(i, fin_row_h.get(i));

        PicturesCollection pics = result_sheet.getPictures();
        ExcelPicture pic;

        for(int i = 0;i < pics.size();i++) {
            pic = pics.get(i);
            tmp1 = BigDecimal.valueOf(
                    result_sheet.get(pic.getTopRow(), pic.getLeftColumn()).getColumnWidth()
            ).divide(BigDecimal.valueOf(pic.getWidth()), 10, BigDecimal.ROUND_DOWN);
            tmp2 = BigDecimal.valueOf(
                    result_sheet.get(pic.getTopRow(), pic.getLeftColumn()).getRowHeight()
            ).divide(BigDecimal.valueOf(pic.getHeight()), 10, BigDecimal.ROUND_DOWN);
            k = BigDecimal.valueOf(0.85).divide(BigDecimal.valueOf(0.09), 10, BigDecimal.ROUND_DOWN);
            k = k.multiply(tmp1.min(tmp2));
            pic.setWidth(2 * (k.multiply(BigDecimal.valueOf(pic.getWidth()))).intValue());
            pic.setHeight(2 * (k.multiply(BigDecimal.valueOf(pic.getHeight()))).intValue());
            pic.setLeftColumnOffset(0);
            pic.setTopRowOffset(0);
            pic.setTop(pic.getTop() +
                    (int) ((result_sheet.get(pic.getTopRow(), pic.getLeftColumn()).getRowHeight() * 1.345
                            - pic.getHeight()) / 2)
            );
        }

        String FilePathWithOutExtension = FileUploadHelper.getBasePath() + savedPath + file_name;
        wb_target.saveToFile(FilePathWithOutExtension + ".xlsx", ExcelVersion.Version2016);
        wb_target.dispose();
        result_sheet.saveToPdf(FilePathWithOutExtension + ".pdf");
        JSONObject result = new JSONObject();
        result.put("excel", savedPath + file_name + ".xlsx");
        result.put("pdf", savedPath + file_name + ".pdf");
        return result.toString();
    }
}
